﻿CREATE PROCEDURE [maint].[GetRestoreFileList]
@DeviceName [sysname], @OutputTableName NVARCHAR (36) OUTPUT, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int
	SET @RC = 0

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DeviceName = N''' + ISNULL(@DeviceName,N'NULL') + ''''
		END

	--create output table
	SET @OutputTableName = N'##' + REPLACE(CAST(NEWID() AS nvarchar(36)),N'-',N'')
	IF(@OutputDiagnostics = 1)
		PRINT '	@OutputTableName = ' + ISNULL(@OutputTableName,N'NULL');

	DECLARE @CreateTableStatement nvarchar(4000)
	SET @CreateTableStatement = N'CREATE TABLE ' + @OutputTableName + N' (
	[LogicalName] nvarchar(128)
	,[PhysicalName] nvarchar(260)
	,[Type] char(1)
	,[FileGroupName] nvarchar(128)
	,[Size] numeric(20,0)
	,[MaxSize] numeric(20,0)
	,[FileID] bigint
	,[CreateLSN] numeric(25,0)
	,[DropLSN] numeric(25,0) NULL
	,[UniqueID] uniqueidentifier
	,[ReadOnlyLSN] numeric(25,0) NULL
	,[ReadWriteLSN] numeric(25,0) NULL
	,[BackupSizeInBytes] bigint
	,[SourceBlockSize] int
	,[FileGroupID] int
	,[LogGroupGUID] uniqueidentifier NULL
	,[DifferentialBaseLSN] numeric(25,0) NULL
	,[DifferentialBaseGUID] uniqueidentifier
	,[IsReadOnly] bit
	,[IsPresent] bit
)';

	IF(@OutputDiagnostics = 1)
		PRINT '	@CreateTableStatement = ' + + CHAR(10) + ISNULL(@CreateTableStatement,N'NULL') + CHAR(10);

	EXEC(@CreateTableStatement)
	SET @ERROR = @@ERROR
	IF(@ERROR <> 0)
		BEGIN
			RAISERROR('Failed to create tempory table for device (%s) file list data',16,1,@DeviceName)
			GOTO ERROR_HANDLER
		END;

	--restore device header
	DECLARE @RestoreStatement nvarchar(4000)
	SET @RestoreStatement = N'INSERT ' + @OutputTableName + N' (
	[LogicalName]
	,[PhysicalName]
	,[Type]
	,[FileGroupName]
	,[Size]
	,[MaxSize]
	,[FileID]
	,[CreateLSN]
	,[DropLSN]
	,[UniqueID]
	,[ReadOnlyLSN]
	,[ReadWriteLSN]
	,[BackupSizeInBytes]
	,[SourceBlockSize]
	,[FileGroupID]
	,[LogGroupGUID]
	,[DifferentialBaseLSN]
	,[DifferentialBaseGUID]
	,[IsReadOnly]
	,[IsPresent]
)
EXEC(''RESTORE FILELISTONLY FROM [' + @DeviceName + N'] WITH NOUNLOAD'')';

	IF(@OutputDiagnostics = 1)
		PRINT '	@RestoreStatement = ' + CHAR(10) + ISNULL(@RestoreStatement,N'NULL') + CHAR(10);

	EXEC(@RestoreStatement)
	SET @ERROR = @@ERROR
	IF(@ERROR <> 0)
		BEGIN
			RAISERROR('Failed to restore file list for device %s',16,1,@DeviceName)
			GOTO ERROR_HANDLER
		END;

	GOTO EXIT_PROC

ERROR_HANDLER:
	IF(@RC = 0) SET @RC = @ERROR

EXIT_PROC:
	RETURN(@RC)	
END;